use Caronte2014
GO

SET LANGUAGE ENGLISH
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO

PRINT N'creo la tabella di servizio'
GO
if EXISTS (select * from tempdb..sysobjects where id=object_id('tempdb..listini_to_del')) drop table listini_to_del
create table listini_to_del (codlist char(3))
-- popolo la tabella di servizio con i listini da cancellare
insert into listini_to_del select distinct codlist from listini where codlist not in (select codlist from corse)
GO
IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END
GO

print N'pulisco i prezzi'
GO
DELETE FROM PREZZI WHERE CODLIST IN (SELECT CODLIST FROM listini_to_del)
IF @@ERROR<>0 ROLLBACK TRANSACTION
GO

PRINT N'pulisco configtasse'
GO
DELETE FROM CONFIGTASSE WHERE CODLIST IN (SELECT CODLIST FROM listini_to_del)
IF @@ERROR<>0 ROLLBACK TRANSACTION
GO

PRINT N'pulisco supplementi'
GO
DELETE FROM SUPPLEMENTI WHERE CODLIST IN (SELECT CODLIST FROM LISTINI_TO_DEL)
IF @@ERROR<>0 ROLLBACK TRANSACTION
GO

PRINT N'pulisco listini'
GO
DELETE FROM LISTINI WHERE CODLIST IN (SELECT CODLIST FROM LISTINI_TO_DEL)
IF @@ERROR<>0 ROLLBACK TRANSACTION
GO

-- cancello la tabella di servizio
drop table listini_to_del
GO
COMMIT TRANSACTION
GO